/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package categorybean;

import DBConnection.ATeamObject;
import java.rmi.RemoteException;
import java.sql.*;
import java.util.*;

import javax.ejb.*;
import javax.sql.*;
import javax.naming.*;
/**
 *
 * @author Konstantin
 */
public class CategoryBean implements EntityBean{
    
    private EntityContext entityContext;
    private Connection connection;
    private DataSource dataSource;
    
    private Integer objectId;
    private String name;
    private String objectDescription;
    private Integer parentId;
    private java.util.Date createDate;
    private java.util.Date lastModifyDate;
    
    public CategoryBean(){
        
    }
    
    public Integer getObjectId()
            throws RemoteException{
        return objectId;
    }

    public void setName(ATeamObject aTeamObject)
            throws RemoteException{
        name = aTeamObject.getName();
    }
    
    public String getName()
            throws RemoteException{
        return name;
    }
    
    public java.util.Date getCreateDate(){
        return createDate;
    }
    
    public java.util.Date getLastModifyDate(){
        return lastModifyDate;
    }
    
    public void setObjectDescription(ATeamObject aTeamObject)
            throws RemoteException{
        objectDescription = aTeamObject.getObjectDescription();
    }
    
    public String getObjectDescription()
            throws RemoteException{
        return objectDescription;
    }
    
    public void setParentId(ATeamObject aTeamObject)
            throws RemoteException{
        parentId = new Integer(aTeamObject.getParentId());
    }
    
    public Integer getParentId()
            throws RemoteException{
        return parentId;
    }
    
    public Integer ejbFindByPrimaryKey(Integer primaryKey) 
            throws RemoteException, FinderException{
        try{
            if (connection == null){
                connection = dataSource.getConnection();
            }
            String findByPrimaryKeyQuery = "SELECT Object.ObjectId FROM Object"+
                    " WHERE Object.ObjectId = ?";
            
            PreparedStatement findByPrimaryKeyQueryStat = 
                    connection.prepareStatement(findByPrimaryKeyQuery);
            findByPrimaryKeyQueryStat.setInt(1, primaryKey.intValue());
            ResultSet res = findByPrimaryKeyQueryStat.executeQuery();
            if(res.next()){
                res.close();
                findByPrimaryKeyQueryStat.close();
                closeConnection();
                return primaryKey;
            }
            else{
                throw new FinderException("Category hasn't been found");
            } 
        }
        catch(SQLException sqlException){
            throw new FinderException(sqlException.getMessage());
        } 
    }
    
    public Collection ejbFindByName(ATeamObject aTeamObject)
            throws RemoteException, FinderException{
        try{
            if (connection == null){
                connection = dataSource.getConnection();
            }
            String findByNameQuery = "SELECT Object.ObjectId FROM Object"+
                    " WHERE Object.Name = ?";
            
            PreparedStatement findByNameQueryStat =
                    connection.prepareStatement(findByNameQuery);
            findByNameQueryStat.setString(1, aTeamObject.getName());
            ResultSet res = findByNameQueryStat.executeQuery();
            Collection Keys = new ArrayList();
            if (!res.next()) {
                throw new FinderException("Category hasn't been found.");
            }
            else{
                do{
                    Keys.add(new Integer(res.getInt("ObjectId")));
                }while(res.next());
                res.close();
                findByNameQueryStat.close();
                closeConnection();
                return Keys;
            }
            
        }
        catch(SQLException sqlException){
            throw new FinderException(sqlException.getMessage());
        }
    }
    
    public Collection ejbFindByObjectDescription(ATeamObject aTeamObject)
            throws RemoteException, FinderException{
        try{
            if (connection == null){
                connection = dataSource.getConnection();
            }
            String findByObjectDescriptionQuery = "SELECT Object.ObjectId FROM Object"+
                    " WHERE Object.ObjectDescription = ?";
            
            PreparedStatement findByObjectDescriptionQueryStat =
                    connection.prepareStatement(findByObjectDescriptionQuery);
            findByObjectDescriptionQueryStat.setString(1, aTeamObject.getObjectDescription());
            ResultSet res = findByObjectDescriptionQueryStat.executeQuery();
            Collection Keys = new ArrayList();
            if (!res.next()) {
                throw new FinderException("Category hasn't been found.");
            }
            else{
                do{
                    Keys.add(new Integer(res.getInt("ObjectId")));
                }while(res.next());
                res.close();
                findByObjectDescriptionQueryStat.close();
                closeConnection();
                return Keys;
            }
            
        }
        catch(SQLException sqlException){
            throw new FinderException(sqlException.getMessage());
        }
    }
    
    public Collection ejbFindAll()
            throws RemoteException, FinderException{
        try{
            if (connection == null){
                connection = dataSource.getConnection();
            }
            String findAllQuery = "SELECT Object.ObjectId FROM Object"+
                    " WHERE Object.ObjectTypeId = 1";
            
            PreparedStatement findAllQueryStat =
                    connection.prepareStatement(findAllQuery);
            ResultSet res = findAllQueryStat.executeQuery();
            Collection Keys = new ArrayList();
            if (!res.next()) {
                throw new FinderException("Category hasn't been found.");
            }
            else{
                do{
                    Keys.add(new Integer(res.getInt("ObjectId")));
                }while(res.next());
                res.close();
                findAllQueryStat.close();
                closeConnection();
                return Keys;
            }
            
        }
        catch(SQLException sqlException){
            throw new FinderException(sqlException.getMessage());
        }
    }
    
    public Integer ejbCreate(ATeamObject aTeamObject) 
            throws CreateException, RemoteException{
        try{
            if (connection == null){
                connection = dataSource.getConnection();
            }
            String executeProcedure = "BEGIN"+
                    " GETNEXTSEQ;"+
                    " END;";
            
            PreparedStatement executeProcedureStat = 
                    connection.prepareStatement(executeProcedure);
            executeProcedureStat.execute();
            
            String getNextSeq = "SELECT Objectseq.nextval FROM DUAL";
            PreparedStatement getNextSeqStat = 
                    connection.prepareStatement(getNextSeq);
            ResultSet res = getNextSeqStat.executeQuery();
            res.next();
            int nextseq = res.getInt(1);
            getNextSeqStat.close();
            objectId = new Integer(nextseq);
            
            this.name = aTeamObject.getName();
            this.objectDescription = aTeamObject.getObjectDescription();
            this.parentId = 26;
            
            String insertIntoObject ="INSERT INTO Object VALUES (?,26,?,1,?)";
            
            PreparedStatement insertStatObject =
                    connection.prepareStatement(insertIntoObject);
            insertStatObject.setInt(1,nextseq);
            insertStatObject.setString(2,aTeamObject.getName());
            insertStatObject.setString(3,aTeamObject.getObjectDescription());
            insertStatObject.executeUpdate();
            insertStatObject.close();
            
            String insertDateIntoParams =
            "INSERT INTO Params VALUES(?,?, TO_CHAR(SYSDATE,'DD.MM.YYYY HH24:MI:SS'))";
            PreparedStatement insertDateStat =
                    connection.prepareStatement(insertDateIntoParams);
            insertDateStat.setInt(1, nextseq);
            insertDateStat.setInt(2, 15);
            insertDateStat.executeUpdate();
            
            insertDateStat.setInt(1, nextseq);
            insertDateStat.setInt(2, 16);
            insertDateStat.executeUpdate();
            
            
            insertDateStat.close();
            closeConnection();
            
            return objectId;
        }
        catch(SQLException sqlException){
            throw new CreateException(sqlException.getMessage());
        }
    }
    
    public void ejbPostCreate(ATeamObject aTeamObject){
        
    }
    
    public void setEntityContext(EntityContext ctx)
            throws EJBException, RemoteException {
        entityContext = ctx;
        try{
            InitialContext initialContext = new InitialContext();
            
            dataSource =
                (DataSource) initialContext.lookup("java:/ATeamConnectionPool");           
            connection = dataSource.getConnection();
        }
        catch(NamingException namingException ){
            throw new EJBException( namingException);
        }
        catch(SQLException sqlException){
            throw new EJBException(sqlException);
        }
    }

    public void unsetEntityContext()
            throws EJBException, RemoteException {
        entityContext = null;
        try{
            connection.close();
        }
        catch(SQLException sqlException){
            throw new EJBException(sqlException);
        }
        finally{
            connection = null;
        }
    }

    public void ejbRemove()
            throws RemoveException, EJBException, RemoteException {
        try{
            Integer primaryKey = (Integer) entityContext.getPrimaryKey();
            
            if (connection == null){
                connection = dataSource.getConnection();
            }
            String delete = "DELETE FROM Object WHERE"+
                    " Object.ObjectId = ?";
            
            PreparedStatement deleteStatement = connection.prepareStatement(delete);
            
            deleteStatement.setInt(1, primaryKey.intValue());
            
            deleteStatement.executeUpdate();
            deleteStatement.close();
            closeConnection();
        }
        catch(SQLException sqlException){
            throw new RemoveException(sqlException.getMessage());
        }
    }

    public void ejbActivate()
            throws EJBException, RemoteException {
        
    }

    public void ejbPassivate() 
            throws EJBException, RemoteException {
        
    }

    public void ejbLoad() 
            throws EJBException, RemoteException {
        try{
            Integer primaryKey = (Integer) entityContext.getPrimaryKey();
            
            if (connection == null){
                connection = dataSource.getConnection();
            }
            String selectFromObject = "SELECT * FROM Object WHERE"+
                    " Object.ObjectId = ?";
            PreparedStatement selectStatement = connection.prepareStatement(selectFromObject);
            
            selectStatement.setInt(1, primaryKey.intValue());
            
            ResultSet resultSet = selectStatement.executeQuery();
            
            if(resultSet.next()){
                objectId = new Integer(resultSet.getInt("ObjectId"));
                name = resultSet.getString("Name");
                objectDescription = resultSet.getString("ObjectDescription");
                parentId = new Integer(resultSet.getInt("ParentId"));
            }
            else 
                throw new EJBException("No such category.");
            
            String selectFromParams = 
                    "SELECT TO_DATE(VALUE,'DD.MM.YYYY HH24:MI:SS') AS VALUE, AttrId FROM Params"+
                    " WHERE Params.ObjectId = ?"+
                    " AND AttrId IN (15,16)" +
                    " ORDER BY AttrId";
            
            selectStatement = connection.prepareStatement(selectFromParams);
            
            selectStatement.setInt(1, primaryKey.intValue());
            
            resultSet = selectStatement.executeQuery();
            
            resultSet.next();
            createDate = resultSet.getDate("Value");
            resultSet.next();
            lastModifyDate = resultSet.getDate("Value");            
            selectStatement.close();
            closeConnection();
        }
        catch(SQLException sqlException){
            throw new EJBException(sqlException);
        }
    }

    public void ejbStore() 
            throws EJBException, RemoteException {
        try{
            Integer primaryKey = (Integer) entityContext.getPrimaryKey();
            
            if (connection == null){
                connection = dataSource.getConnection();
            }
            
            String editNameOfCategoryInObjectQuery = 
                "UPDATE Object"+ 
                " SET Object.Name = ?"+
                " WHERE Object.ObjectId = ?";
    
            String editDescriptionOfCategoryQuery =
                "UPDATE Object"+
                " SET Object.ObjectDescription = ?"+
                " WHERE Object.ObjectId = ?";
            
            String editDateOfCategoryQuery = 
                "UPDATE Params"+
                " SET Value = TO_CHAR(SYSDATE,'DD.MM.YYYY HH24:MI:SS')"+
                " WHERE ObjectId = ?"+
                " AND AttrId = 16";
            
            PreparedStatement updateStatement =
                    connection.prepareStatement(editNameOfCategoryInObjectQuery);         
            updateStatement.setString(1, name);
            updateStatement.setInt(2, primaryKey.intValue());
            updateStatement.executeUpdate();
               
            updateStatement =
                    connection.prepareStatement(editDescriptionOfCategoryQuery);
            updateStatement.setString(1, objectDescription);
            updateStatement.setInt(2, primaryKey.intValue());
            updateStatement.executeUpdate();
            
            updateStatement =
                    connection.prepareStatement(editDateOfCategoryQuery);
            updateStatement.setInt(1, primaryKey.intValue());
            updateStatement.executeUpdate();
            
            updateStatement.close();
            
            closeConnection();
        }
        catch(SQLException sqlException){
            throw new EJBException(sqlException);
        }
    }
    
    void closeConnection() {
        try {
            if (connection != null){
                connection.close();
                System.out.println("Connection closed");
            }
        } catch (SQLException e) {
            throw new EJBException("closeConnection:" + e.getMessage());
        } finally {
            connection = null;
        }
    }
    
}
